﻿using BCCommon;
using BCEntity.TMS.Agent.AgentCurrentlyProtocol;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace BCData.TMS.Agent.AgentCurrentlyProtocol
{
    public class AgentCurrentlyProtocolData : IAgentCurrentlyProtocolData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT A.AgentCurrentlyProtocolId,A.AgentRecordId,A.ProtocolType,A.ProtocolContent,A.SignDate,A.CreateTime 
                                    FROM AgentCurrentlyProtocol A";

        private const string QUERY_JOIN = @"SELECT A.AgentCurrentlyProtocolId,A.AgentRecordId,A.ProtocolType,A.ProtocolContent,A.SignDate,A.CreateTime 
                                    FROM AgentCurrentlyProtocol A 
                                    INNER JOIN AgentRecord B ON A.AgentRecordId = B.AgentRecordId";

        public AgentCurrentlyProtocolData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<AgentCurrentlyProtocolEntity> GetEntityByAgentRecordId(long agentRecordId)
        {
            return await database.Slave.QueryEntityAsync<AgentCurrentlyProtocolEntity>($"{QUERY} WHERE A.AgentRecordId = ?AgentRecordId", new { agentRecordId });
        }

        public async Task<AgentCurrentlyProtocolEntity> Insert(AgentCurrentlyProtocolEntity entity)
        {
            return await database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<AgentCurrentlyProtocolEntity> GetEntity(long agentCurrentlyProtocolId)
        {
            return await database.Slave.QueryEntityAsync<AgentCurrentlyProtocolEntity>($"{QUERY} WHERE A.AgentCurrentlyProtocolId = ?AgentCurrentlyProtocolId", new { agentCurrentlyProtocolId });
        }

        public async Task<AgentCurrentlyProtocolEntity> Update(AgentCurrentlyProtocolEntity entity)
        {
            return await database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<int> Delete(long agentCurrentlyProtocolId)
        {
            return await database.Master.ExecuteAsync("DELETE FROM AgentCurrentlyProtocol WHERE A.AgentCurrentlyProtocolId = ?AgentCurrentlyProtocolId", new { agentCurrentlyProtocolId }, database.Transaction);
        }

        public async Task<(IEnumerable<AgentCurrentlyProtocolEntity>, int)> Query(int? logonCmpanyId, DateTime? signStartDate, DateTime? signEndDate, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Empty;
            if (signStartDate.HasValue)
            {
                wheres.Add("A.SignDate >= ?SignStartDate");
                parameters.Add("?SignStartDate", signStartDate.Value.Date, DbType.Date);
            }
            if (signEndDate.HasValue)
            {
                wheres.Add("A.SignDate < ?SignEndDate");
                parameters.Add("?SignEndDate", signEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            if (logonCmpanyId.HasValue)
            {
                wheres.Add("FIND_IN_SET(B.CompanyPathCode,?LogonCmpanyId)");
                parameters.Add("?LogonCmpanyId", logonCmpanyId.Value, DbType.Int32);
                sql = string.Format(@"{0} {1} ORDER BY A.AgentRecordId DESC LIMIT {2},{3};
                                    SELECT COUNT(1) FROM AgentCurrentlyProtocol A 
                                    INNER JOIN AgentRecord B ON A.AgentRecordId = B.AgentRecordId {1}",
                                    QUERY_JOIN,
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                                    (pageIndex - 1) * pageSize,
                                    pageSize);
            }
            else
            {
                sql = string.Format(@"{0} {1} ORDER BY A.AgentRecordId DESC LIMIT {2},{3};
                                    SELECT COUNT(1) FROM AgentCurrentlyProtocol A {1}",
                                    QUERY,
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                                    (pageIndex - 1) * pageSize,
                                    pageSize);
            }
            return await database.Slave.QueryPageableListAsync<AgentCurrentlyProtocolEntity>(sql, parameters);
        }
    }
}
